/***********************************************************************************************************************************
 * Name:	Server_Properties.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Fetch useful information about the server.
 */
SET IMPLICIT_TRANSACTIONS OFF; WHILE 1 < @@TRANCOUNT ROLLBACK;
USE [master];
GO

DECLARE @Value SQL_VARIANT;
DECLARE @ServerProperties TABLE (RowID TINYINT NOT Null IDENTITY(1,1) PRIMARY KEY CLUSTERED, [Name] SYSNAME NOT Null, [Value] SQL_VARIANT Null);
DECLARE @msver TABLE ([Index] TINYINT NOT Null PRIMARY KEY CLUSTERED, [Name] SYSNAME NOT Null, [Internal_Value] INTEGER Null, [Character_Value] SYSNAME Null);
SET NOCOUNT ON;

INSERT INTO @ServerProperties (Name, Value) VALUES	(N'Server Name'		, SERVERPROPERTY('ServerName'));
INSERT INTO @ServerProperties (Name, Value) VALUES	(N'Machine Name'	, SERVERPROPERTY('MachineName'));
INSERT INTO @ServerProperties (Name, Value) VALUES	(N'NetBIOS Name'	, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));
INSERT INTO @ServerProperties (Name, Value) VALUES	(N'Is Clustered'	, SERVERPROPERTY('IsClustered'));
INSERT INTO @ServerProperties (Name, Value) VALUES	(N'Product Version'	, SERVERPROPERTY('ProductVersion'));
INSERT INTO @ServerProperties (Name, Value) VALUES	(N'Edition'			, SERVERPROPERTY('Edition'));

INSERT INTO @msver EXEC master.sys.xp_msver;
INSERT INTO @ServerProperties SELECT [Name], [Internal_Value] FROM @msver WHERE [Index] IN (16,19);

INSERT INTO @ServerProperties (Name, Value) SELECT N'Uptime (Days)', DATEDIFF(dd,create_date,CURRENT_TIMESTAMP) FROM sys.databases WHERE name='tempdb';

EXEC master.dbo.xp_instance_regread
	@rootkey		= N'HKEY_LOCAL_MACHINE',
	@key			= N'SOFTWARE\Microsoft\MSSQLServer\Setup',
	@value_name		= N'SQLDataRoot',
	@value			= @Value OUTPUT;
INSERT INTO @ServerProperties (Name, Value) VALUES (N'SQL Data Root', @Value); SET @Value= Null;

EXEC master.dbo.xp_instance_regread
	@rootkey		= N'HKEY_LOCAL_MACHINE',
	@key			= N'Software\Microsoft\MSSQLServer\MSSQLServer',
	@value_name		= N'DefaultData',
	@value			= @Value OUTPUT;
IF @Value IS Null SET @Value= SERVERPROPERTY('InstanceDefaultDataPath');
INSERT INTO @ServerProperties (Name, Value) VALUES (N'Default Data Directory', @Value); SET @Value= Null;

EXEC master.dbo.xp_instance_regread
	@rootkey		= N'HKEY_LOCAL_MACHINE',
	@key			= N'Software\Microsoft\MSSQLServer\MSSQLServer',
	@value_name		= N'DefaultLog',
	@value			= @Value OUTPUT;
IF @Value IS Null SET @Value= SERVERPROPERTY('InstanceDefaultLogPath');
INSERT INTO @ServerProperties (Name, Value) VALUES (N'Default Log Directory', @Value); SET @Value= Null;

EXEC master.dbo.xp_instance_regread
	@rootkey		= N'HKEY_LOCAL_MACHINE',
	@key			= N'Software\Microsoft\MSSQLServer\MSSQLServer',
	@value_name		= N'BackupDirectory',
	@value			= @Value OUTPUT;
INSERT INTO @ServerProperties (Name, Value) VALUES (N'Backup Directory', @Value); SET @Value= Null;

EXECUTE master.sys.xp_instance_regread
	@rootkey	= N'HKEY_LOCAL_MACHINE',
	@key		= N'SOFTWARE\Microsoft\Microsoft SQL Server\SQLServerAgent',
	@value_name	= N'WorkingDirectory',
	@value			= @Value OUTPUT;
INSERT INTO @ServerProperties (Name, Value) VALUES (N'Agent Working Directory', @Value); SET @Value= Null;

SELECT Name, Value FROM @ServerProperties ORDER BY RowID;
SET NOCOUNT OFF;
GO